package CAT200;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSetMetaData;


public class Database_admin
{
    private static String dbURL = "jdbc:derby://localhost:1527/CAT200-DB;create=true;user=user;password=123";
    private static String tableName = "ADMIN";	// 
    // jdbc Connection
    private static Connection conn = null;
    private static Statement stmt = null;

    public static void main(String[] args)
    {
        createConnection();

        createTable();
        insertData("1000", "Wai Hong2",  "90875554333",
     		   "016666668","admin1",  "waihong",  "Universiti Sains", "019601","sg dua","penang","wai@yahoo.com","010210");
        insertData("1001", "Shiela2",  "90875554334",
       		   "016657588","admin2",  "shiela",  "Universiti Sains", "019601","sg dua","penang","shiela@yahoo.com","010310");

       
        displayData();
        shutdown();
    }


    //  to establish connection
    private static void createConnection()
    {
        try
        {
            //DriverManager.registerDriver(new org.apache.derby.jdbc.ClientDriver());
            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            //Get a connection
            conn = DriverManager.getConnection(dbURL);
        }
        catch (Exception except)
        {
            except.printStackTrace();
        }
    }



    // to create table
    private static void createTable()
    {
    	try
    	{
    		Statement stmt = conn.createStatement();
    		// Create table called my_table
    		String sql = "create table "+ tableName +
    					 "("+
    					 	"STAFF_ID 			VARCHAR(10)	unique,"		+
    					 	"STAFF_NAME 		CHAR(40)	,"		+
    					 	"STAFF_IC 		    VARCHAR(20)	unique,"		+
    					 	"STAFF_TEL 			VARCHAR(20)	,"		+
    					 	"STAFF_USERNAME 	VARCHAR(20)	,"		+
    					 	"STAFF_PASS 		VARCHAR(15)	,"		+
    					 	"STAFF_ADD       	CHAR(60)	,"		+
    					 	"STAFF_POSCODE  	VARCHAR(11)	,"		+
    					 	"STAFF_CITY  	   VARCHAR(30)	,"		+
    					 	"STAFF_STATE  	   VARCHAR(20)	,"		+
    					 	"STAFF_EMAIL  	   VARCHAR(30)	,"		+
    					 	"STAFF_DATEOFJOIN  VARCHAR(30)	"		+
    					 ")";

    		stmt.executeUpdate(sql);
    	}
    	catch (SQLException e)
    	{

    	}

    }

    // to insert data
    private static void insertData(String id, String name, String NRic,String Tel,String username,String pass,
    		String address,String poscode,String city,String state,String email,String datejoin)
    {
        try
        {
            stmt = conn.createStatement();


            stmt.execute("insert into " + tableName +" values " +
            		     	"("	+
            		     		"'"	+	id			+ 	  "',"+
            		     		"'"	+	name		+ 	  "',"+
            		     		"'"	+	NRic	+ 	  "',"+
            		     		"'"	+	Tel			+ 	  "',"+
            		     		"'"	+	username			+ 	  "',"+
            		     		"'"	+	pass		+ 	  "',"+
            		     		"'"	+	address		+ 	  "',"+
            		     		"'"	+	poscode		+ 	  "',"+
            		     		"'"	+	city		+ 	  "',"+
            		     		"'"	+	state		+ 	  "',"+
            		     		"'"	+	email		+ 	  "',"+
            		     		"'"	+	datejoin		+ 	  "'"+
            		     	")"
            		     );




            stmt.close();




        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
    }



    // to display the table
    
    private static void displayData()
    {
        try
        {
            stmt = conn.createStatement();
            ResultSet results = stmt.executeQuery("select * from " + tableName);
            ResultSetMetaData rsmd = results.getMetaData();
            int numberCols = rsmd.getColumnCount();
            for (int i=1; i<=numberCols; i++)
            {
                //print Column Names
                System.out.print(rsmd.getColumnLabel(i)+"\t\t");
            }

            System.out.println("\n-------------------------------------------------");

            while(results.next())
            {
                String id = results.getString(1);
                String name = results.getString(2);

                System.out.println(id + "\t\t" + name);
            }
            results.close();
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
    }
    


    // to shut down the connection
    private static void shutdown()
    {
        try
        {
            if (stmt != null)
            {
                stmt.close();
            }
            if (conn != null)
            {
                DriverManager.getConnection(dbURL + ";shutdown=true");
                conn.close();
            }
        }
        catch (SQLException sqlExcept)
        {

        }

    }
}
